
/**		
 * 
 *	uk.co.lmfm.data.SQLManager
 *	
 *	@version 1.00 | Jan 13, 2010
 *	@author Justin Windle
 *  
 **/
 
package uk.co.lmfm.data 
{
	import org.osflash.thunderbolt.Logger;

	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.errors.SQLError;
	import flash.events.EventDispatcher;
	import flash.events.SQLErrorEvent;
	import flash.filesystem.File;
	import flash.utils.describeType;

	/**
	 * SQLManager
	 */
	public class SQLManager extends EventDispatcher 
	{
		//	----------------------------------------------------------------
		//	PRIVATE MEMBERS
		//	----------------------------------------------------------------

		private var _connection : SQLConnection = new SQLConnection();

		//	----------------------------------------------------------------
		//	CONSTRUCTOR
		//	----------------------------------------------------------------

		public function SQLManager(database : File)
		{
			super();
			connect(database);
		}

		//	----------------------------------------------------------------
		//	PUBLIC METHODS
		//	----------------------------------------------------------------

		public function connect(database : File) : void
		{
			_connection.addEventListener(SQLErrorEvent.ERROR, onSQLError);
			_connection.open(database);
		}

		// TODO Determine what kind of value to insert (e.g. TEXT INT etc)
		
		public function writeObject(obj : Object, table : String) : void
		{
			var xml : XML = describeType(obj);
			var props : XMLList = xml..variable;
			var count : int = props.length();
			var update : SQLStatement;
			var prop : String;
			var i : int;
			
			var create : String = "CREATE TABLE IF NOT EXISTS " + table + " (";
			
			for (i = 0;i < count;i++)
			{
				prop = props[i].@name;
				create += prop + " TEXT" + (i < count - 1 ? ", " : ")");
			}
				
			execute(create);
			
			update = new SQLStatement();
			update.text = "INSERT INTO " + table + " (";

			for (i = 0;i < count;i++)
			{
				prop = props[i].@name;
				update.text += prop + (i < count - 1 ? ", " : ") VALUES (");
			}
			
			for (i = 0;i < count;i++)
			{
				prop = props[i].@name;
				update.text += ":" + prop + (i < count - 1 ? ", " : ")");
				update.parameters[":" + prop] = obj[prop];
			}
			
			execute(update);
		}

		public function readObject(def : Class, table : String) : Object
		{
			var xml : XML = describeType(def);
			var props : XMLList = xml..variable;
			var count : int = props.length();
			var prop : String;
			var i : int;
			
			var query : SQLStatement = new SQLStatement();
			query.text = "SELECT ";
			
			for (i = 0;i < count;i++)
			{
				prop = props[i].@name;
				query.text += prop + (i < count - 1 ? ", " : "");
			}
			
			query.text += " FROM " + table;
			
			if(!execute(query))
			{
				Logger.error("Error retrieving content from database");
				return null;
			}
			
			var obj : * = new def();
			var result : SQLResult = query.getResult();
			var pos : int = result.data.length - 1;
			
			for (var col:String in result.data[pos])
			{
				obj[col] = result.data[pos][col];
			} 
			
			return obj;
		}

		public function execute(query : *) : SQLStatement
		{
			var statement : SQLStatement;
			
			if(query is String)
			{
				Logger.info("Executing SQL statement: " + query);
				statement = new SQLStatement();
				statement.text = query;
			}
			else if(query is SQLStatement)
			{
				Logger.info("Executing SQL statement: " + query.text);
				statement = query;
			}
			
			try
			{
				statement.sqlConnection = _connection;
				statement.execute();
				return statement;
			}
			catch (error : SQLError) 
			{ 
				Logger.error(error.message); 
			}
			
			return null;
		}
		
		//	----------------------------------------------------------------
		//	EVENT HANDLERS
		//	----------------------------------------------------------------

		private function onSQLError(event : SQLErrorEvent) : void
		{
			dispatchEvent(event);
		}
	}
}
